﻿
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Archive].[View_Archives]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [Archive].[View_Archives]
GO
CREATE VIEW Archive.View_Archives
AS
SELECT     a.Id, a.Name, a.QueryNumber, a.OrderNumber, a.SecretLevel, a.Province, a.City, a.County, a.ContractCode, a.FiledDate, a.CustomerId, a.CategoryId, 
                      a.Scale, a.ConstructForm, a.DesignStage, a.BelongedTo, a.ProjectManager, a.BuildingBlueprint, a.ElectricalBlueprint, a.MechanicalMaterialManifest, 
                      a.ElectricalMaterialManifest, a.SystemSolution, a.ElectricalSolution, a.Executor, a.IsDeleted, a.IsActive, a.CreatedTime, a.CreatedBy, a.ModifiedTime, 
                      a.LastModifiedBy, a.Version, a.Description, c.Name AS Customer, c.Address, c.Telephone1, c.Telephone2, c.Fax, c.Email, c.Contact, c.ContactTitle, 
                      c.ContactPhone, t.Name AS Category, a.DesignSketch, a.TenderDocumentation, a.PreliminaryDesign, a.OtherDocumentation, a.KeyWords, 
                      a.DesignedYear
FROM         Archive.Archive AS a INNER JOIN
                      Archive.Category AS t ON a.CategoryId = t.Id INNER JOIN
                      Archive.Customer AS c ON a.CustomerId = c.Id                      
GO

/****** 对象:  StoredProcedure [Archive].[Archive_InsertArchive]    脚本日期: 11/12/2010 17:37:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archive].[Archive_InsertArchive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Archive].[Archive_InsertArchive]
GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchive]    脚本日期: 11/12/2010 17:37:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archive].[Archive_SelectArchive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Archive].[Archive_SelectArchive]
GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchivesAll]    脚本日期: 11/12/2010 17:37:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archive].[Archive_SelectArchivesAll]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Archive].[Archive_SelectArchivesAll]
GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchivesPaged]    脚本日期: 11/12/2010 17:37:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archive].[Archive_SelectArchivesPaged]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Archive].[Archive_SelectArchivesPaged]
GO
/****** 对象:  StoredProcedure [Archive].[Archive_UpdateArchive]    脚本日期: 11/12/2010 17:37:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Archive].[Archive_UpdateArchive]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Archive].[Archive_UpdateArchive]
GO


/****** 对象:  StoredProcedure [Archive].[Archive_InsertArchive]    脚本日期: 11/12/2010 17:37:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [Archive].[Archive_InsertArchive]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   husb using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [Archive].[Archive_InsertArchive]
-- Date Generated: 2009-8-5 15:42:48
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Archive].[Archive_InsertArchive]
	@Id uniqueidentifier
	,@Name varchar(256)
	,@QueryNumber varchar(256)
	,@OrderNumber int
	,@SecretLevel varchar(256)
	,@Province varchar(256)
	,@City varchar(256)
	,@County varchar(256)
	,@ContractCode varchar(256)
	,@FiledDate datetime
	,@CustomerId uniqueidentifier
	,@CategoryId uniqueidentifier
	,@Scale int
	,@ConstructForm varchar(256)
	,@DesignStage varchar(256)
	,@BelongedTo varchar(256)
	,@ProjectManager varchar(256)
	,@BuildingBlueprint nvarchar(256)
	,@ElectricalBlueprint nvarchar(256)
	,@MechanicalMaterialManifest nvarchar(256)
	,@ElectricalMaterialManifest nvarchar(256)
	,@SystemSolution nvarchar(256)
	,@ElectricalSolution nvarchar(256)
	,@DesignSketch varchar(384)
	,@TenderDocumentation varchar(384)
	,@PreliminaryDesign varchar(384)
	,@OtherDocumentation varchar(384)
	
	,@KeyWords varchar(384)
	,@DesignedYear varchar(384)

	,@Customer varchar(256)
	,@Address varchar(256)
	,@Telephone1 varchar(256)
	,@Telephone2 varbinary
	,@Fax varchar(256)
	,@Email varchar(256)
	,@Contact varchar(256)
	,@ContactTitle varchar(256)
	,@ContactPhone varchar(256)
	
	,@Executor varchar(384)

	,@IsDeleted bit
	,@IsActive bit
	,@CreatedTime datetime
	,@CreatedBy uniqueidentifier
	,@ModifiedTime datetime
	,@LastModifiedBy uniqueidentifier
	,@Version int
	,@Description nvarchar(3072)
AS

SET NOCOUNT ON

-- SET @Id = NEWID()
IF (@IsDeleted IS NULL) SET @IsDeleted = 0
IF (@IsActive IS NULL) SET @IsActive = 1
IF (@CreatedTime IS NULL) SET @CreatedTime = GETDATE()
IF (@ModifiedTime IS NULL) SET @ModifiedTime = GETDATE()
IF (@Version IS NULL) SET @Version = 1

BEGIN TRANSACTION

EXEC Archive.Customer_InsertCustomer 
	@CustomerId
	,@Customer
	,''
	,@Address
	,@Telephone1
	,@Telephone2
	,@Fax
	,@Email
	,@Contact
	,@ContactTitle
	,@ContactPhone
	
	,@IsDeleted
	,@IsActive
	,@CreatedTime
	,@CreatedBy
	,@ModifiedTime
	,@LastModifiedBy
	,@Version
	,@Description

INSERT INTO [Archive].[Archive] (
	[Id]
	,[Name]
	,[QueryNumber]
	,[OrderNumber]
	,[SecretLevel]
	,[Province]
	,[City]
	,[County]
	,[ContractCode]
	,[FiledDate]
	,[CustomerId]
	,[CategoryId]
	,[Scale]
	,[ConstructForm]
	,[DesignStage]
	,[BelongedTo]
	,[ProjectManager]
	,[BuildingBlueprint]
	,[ElectricalBlueprint]
	,[MechanicalMaterialManifest]
	,[ElectricalMaterialManifest]
	,[SystemSolution]
	,[ElectricalSolution]
	,[DesignSketch]
	,[TenderDocumentation]
	,[PreliminaryDesign]
	,[OtherDocumentation]
	,[KeyWords]
	,[DesignedYear]

	,[Executor]

	,[IsDeleted]
	,[IsActive]
	,[CreatedTime]
	,[CreatedBy]
	,[ModifiedTime]
	,[LastModifiedBy]
	,[Version]
	,[Description]
) VALUES (
	@Id
	,@Name
	,@QueryNumber
	,@OrderNumber
	,@SecretLevel
	,@Province
	,@City
	,@County
	,@ContractCode
	,@FiledDate
	,@CustomerId
	,@CategoryId
	,@Scale
	,@ConstructForm
	,@DesignStage
	,@BelongedTo
	,@ProjectManager
	,@BuildingBlueprint
	,@ElectricalBlueprint
	,@MechanicalMaterialManifest
	,@ElectricalMaterialManifest
	,@SystemSolution
	,@ElectricalSolution
	,@DesignSketch
	,@TenderDocumentation
	,@PreliminaryDesign
	,@OtherDocumentation
	,@KeyWords
	,@DesignedYear

	,@Executor

	,@IsDeleted
	,@IsActive
	,@CreatedTime
	,@CreatedBy
	,@ModifiedTime
	,@LastModifiedBy
	,@Version
	,@Description
)

IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('添加档案失败.', 16, 1)
    RETURN
 END

COMMIT
--endregion




GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchive]    脚本日期: 11/12/2010 17:37:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [Archive].[Archive_SelectArchive]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   husb using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [Archive].[Archive_SelectArchive]
-- Date Generated: 2009-8-5 15:42:48
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Archive].[Archive_SelectArchive]
	@Id uniqueidentifier
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT     Id, [Name], QueryNumber, OrderNumber, SecretLevel, Province, City, County, ContractCode, FiledDate, CustomerId, CategoryId, Scale, ConstructForm, DesignStage, BelongedTo, 
                      ProjectManager, BuildingBlueprint, ElectricalBlueprint, MechanicalMaterialManifest, ElectricalMaterialManifest, SystemSolution, ElectricalSolution, [DesignSketch], [TenderDocumentation], [PreliminaryDesign], [OtherDocumentation], [KeyWords], [DesignedYear],
                      Executor, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, [Description], Customer, [Address], Telephone1, Telephone2, Fax, 
                      Email, Contact, ContactTitle, ContactPhone, Category
FROM         Archive.View_Archives
WHERE
	[Id] = @Id

--endregion




GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchivesAll]    脚本日期: 11/12/2010 17:37:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [Archive].[Archive_SelectArchivesAll]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   husb using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [Archive].[Archive_SelectArchivesAll]
-- Date Generated: 2009-8-5 15:42:48
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Archive].[Archive_SelectArchivesAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT     Id, [Name], QueryNumber, OrderNumber, SecretLevel, Province, City, County, ContractCode, FiledDate, CustomerId, CategoryId, Scale, ConstructForm, DesignStage, BelongedTo, 
                      ProjectManager, BuildingBlueprint, ElectricalBlueprint, MechanicalMaterialManifest, ElectricalMaterialManifest, SystemSolution, ElectricalSolution, [DesignSketch], [TenderDocumentation], [PreliminaryDesign], [OtherDocumentation], [KeyWords], [DesignedYear],
                      Executor, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, [Description], Customer, [Address], Telephone1, Telephone2, Fax, 
                      Email, Contact, ContactTitle, ContactPhone, Category
FROM         Archive.View_Archives

--endregion




GO
/****** 对象:  StoredProcedure [Archive].[Archive_SelectArchivesPaged]    脚本日期: 11/12/2010 17:37:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [Archive].[Archive_SelectArchivesPaged]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   husb using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [Archive].[Archive_SelectArchivesPaged]
-- Date Generated: 2009-8-5 15:42:48
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Archive].[Archive_SelectArchivesPaged]
	@startRowIndex		int,
	@maximumRows		int,
	@sortExpression		nvarchar(256),
	@WhereCondition nvarchar(2800) = NULL,
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

IF (@sortExpression IS NULL OR LEN(@sortExpression) = 0)
	SET @sortExpression = 'CreatedTime DESC'
	
IF (@WhereCondition IS NULL OR LEN(@WhereCondition) = 0 OR @WhereCondition = '1 = 1 ')
	SET @WhereCondition = ''
ELSE
	SET @WhereCondition = ' WHERE ' + @WhereCondition

SET @startRowIndex = @startRowIndex + 1


DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT     Id, [Name], QueryNumber, OrderNumber, SecretLevel, Province, City, County, ContractCode, FiledDate, CustomerId, CategoryId, Scale, ConstructForm, DesignStage, BelongedTo, 
                      ProjectManager, BuildingBlueprint, ElectricalBlueprint, MechanicalMaterialManifest, ElectricalMaterialManifest, SystemSolution, ElectricalSolution, [DesignSketch], [TenderDocumentation], [PreliminaryDesign], [OtherDocumentation], [KeyWords], [DesignedYear],
                      Executor, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, Description, Customer, Address, Telephone1, Telephone2, Fax, 
                      Email, Contact, ContactTitle, ContactPhone, Category
FROM
	(SELECT     Id, [Name], QueryNumber, OrderNumber, SecretLevel, Province, City, County, ContractCode, FiledDate, CustomerId, CategoryId, Scale, ConstructForm, DesignStage, BelongedTo, 
                      ProjectManager, BuildingBlueprint, ElectricalBlueprint, MechanicalMaterialManifest, ElectricalMaterialManifest, SystemSolution, ElectricalSolution, [DesignSketch], [TenderDocumentation], [PreliminaryDesign], [OtherDocumentation], [KeyWords], [DesignedYear],
                      Executor, IsDeleted, IsActive, CreatedTime, CreatedBy, ModifiedTime, LastModifiedBy, Version, Description, Customer, Address, Telephone1, Telephone2, Fax, 
                      Email, Contact, ContactTitle, ContactPhone, Category
	,ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum
	FROM
	[Archive].View_Archives
	' + @WhereCondition + ') as tmp
WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) + 
				' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + ' 
				+ CONVERT(nvarchar(10), @maximumRows) + ') - 1'

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + '
ORDER BY
	' + @OrderByExpression
END
EXEC sp_executesql @SQL

GO
/****** 对象:  StoredProcedure [Archive].[Archive_UpdateArchive]    脚本日期: 11/12/2010 17:37:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--region [Archive].[Archive_UpdateArchive]

------------------------------------------------------------------------------------------------------------------------
-- Generated By:   husb using CodeSmith 5.0.0.0
-- Template:       StoredProcedures.cst
-- Procedure Name: [Archive].[Archive_UpdateArchive]
-- Date Generated: 2009-8-5 15:42:48
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Archive].[Archive_UpdateArchive]
	@Id uniqueidentifier
	,@Name varchar(256)
	,@QueryNumber varchar(256)
	,@OrderNumber int
	,@SecretLevel varchar(256)
	,@Province varchar(256)
	,@City varchar(256)
	,@County varchar(256)
	,@ContractCode varchar(256)
	,@FiledDate datetime
	,@CustomerId uniqueidentifier
	,@CategoryId uniqueidentifier
	,@Scale int
	,@ConstructForm varchar(256)
	,@DesignStage varchar(256)
	,@BelongedTo varchar(256)
	,@ProjectManager varchar(256)
	,@BuildingBlueprint nvarchar(256)
	,@ElectricalBlueprint nvarchar(256)
	,@MechanicalMaterialManifest nvarchar(256)
	,@ElectricalMaterialManifest nvarchar(256)
	,@SystemSolution nvarchar(256)
	,@ElectricalSolution nvarchar(256)

	,@DesignSketch varchar(384)
	,@TenderDocumentation varchar(384)
	,@PreliminaryDesign varchar(384)
	,@OtherDocumentation varchar(384)

	,@KeyWords varchar(384)
	,@DesignedYear varchar(384)
	
	,@Customer varchar(256)
	,@Address varchar(256)
	,@Telephone1 varchar(256)
	,@Telephone2 varbinary
	,@Fax varchar(256)
	,@Email varchar(256)
	,@Contact varchar(256)
	,@ContactTitle varchar(256)
	,@ContactPhone varchar(256)

	,@Executor varchar(384)
	
	,@IsDeleted bit
	,@IsActive bit
	,@CreatedTime datetime
	,@CreatedBy uniqueidentifier
	,@ModifiedTime datetime
	,@LastModifiedBy uniqueidentifier
	,@Version int
	,@Description nvarchar(3072)
AS

SET NOCOUNT ON

BEGIN TRANSACTION
EXEC Archive.Customer_UpdateCustomer 
	@CustomerId
	,@Customer
	,''
	,@Address
	,@Telephone1
	,@Telephone2
	,@Fax
	,@Email
	,@Contact
	,@ContactTitle
	,@ContactPhone
	
	,@IsDeleted
	,@IsActive
	,@CreatedTime
	,@CreatedBy
	,@ModifiedTime
	,@LastModifiedBy
	,@Version
	,@Description

UPDATE [Archive].[Archive] SET
	[Name] = @Name
	,[QueryNumber] = @QueryNumber
	,[OrderNumber] = @OrderNumber
	,[SecretLevel] = @SecretLevel
	,[Province] = @Province
	,[City] = @City
	,[County] = @County
	,[ContractCode] = @ContractCode
	,[FiledDate] = @FiledDate
	,[CustomerId] = @CustomerId
	,[CategoryId] = @CategoryId
	,[Scale] = @Scale
	,[ConstructForm] = @ConstructForm
	,[DesignStage] = @DesignStage
	,[BelongedTo] = @BelongedTo
	,[ProjectManager] = @ProjectManager
	,[BuildingBlueprint] = @BuildingBlueprint
	,[ElectricalBlueprint] = @ElectricalBlueprint
	,[MechanicalMaterialManifest] = @MechanicalMaterialManifest
	,[ElectricalMaterialManifest] = @ElectricalMaterialManifest
	,[SystemSolution] = @SystemSolution
	,[ElectricalSolution] = @ElectricalSolution
	,[DesignSketch] = @DesignSketch
	,[TenderDocumentation] = @TenderDocumentation
	,[PreliminaryDesign] = @PreliminaryDesign
	,[OtherDocumentation] = @OtherDocumentation
	,[KeyWords] = @KeyWords
	,[DesignedYear] = @DesignedYear

	,[Executor] = @Executor

	,[IsDeleted] = @IsDeleted
	,[IsActive] = @IsActive
	,[CreatedTime] = @CreatedTime
	,[CreatedBy] = @CreatedBy
	,[ModifiedTime] = @ModifiedTime
	,[LastModifiedBy] = @LastModifiedBy
	,[Version] = @Version
	,[Description] = @Description
WHERE
	[Id] = @Id

IF @@ERROR <> 0
 BEGIN
    ROLLBACK
    RAISERROR ('更新档案失败.', 16, 1)
    RETURN
 END

COMMIT

--endregion
GO

